package com.baijiaoxi.utils.excel;


import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Created by shixj on 2017/9/7.
 */
@Slf4j
public class ExcelUtil {
    @Deprecated
    public static byte[] export(ExcelExport excelExport) {
        return export(excelExport.getSheetTitle(), excelExport.getColumns(), excelExport.getList(), excelExport.getColumnFormatMaps());
    }

    @Deprecated
    public static byte[] export(String sheetTitle, Map<String, String> columns, List<Object> list, Map<String, Map> columnFormatMaps) {
        Set<String> titles = columns.keySet();
        //创建excel表

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetTitle);
        sheet.setDefaultColumnWidth(20);//设置默认行宽
        //表头样式（加粗，水平居中，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置边框样式
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        HSSFFont fontStyle = wb.createFont();
        fontStyle.setBold(true);
        cellStyle.setFont(fontStyle);

        //标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);

        //设置边框样式
        cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框

        //字段样式（垂直居中）
        HSSFCellStyle cellStyle3 = wb.createCellStyle();
        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle3.setShrinkToFit(true);//自适应大小
        /*  cellStyle3.setWrapText(true);*/ //默认采取内容中的\n换行 setWrapText自动换行
        //设置边框样式
        cellStyle3.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle3.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle3.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle3.setBorderRight(BorderStyle.THIN);//右边框

        //创建表头
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);//行高

        HSSFCell cell = row.createCell(0);
        cell.setCellValue(sheetTitle);
        cell.setCellStyle(cellStyle);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (titles.size() - 1)));

        //创建标题
        HSSFRow rowTitle = sheet.createRow(1);
        rowTitle.setHeightInPoints(20);

        HSSFCell hc;
        int columnIndex = 0;
        Map<String, Integer> indexMap = new HashMap<>();
        for (String columnTitle : titles) {
            hc = rowTitle.createCell(columnIndex);
            hc.setCellValue(columns.get(columnTitle));
            hc.setCellStyle(cellStyle2);
            indexMap.put(columnTitle, columnIndex);
            columnIndex += 1;

        }

        byte result[] = null;

        ByteArrayOutputStream out = null;

        try {
            //创建表格数据
            Field[] fields;
            int i = 2;

            for (Object obj : list) {
                fields = obj.getClass().getDeclaredFields();

                HSSFRow rowBody = sheet.createRow(i);
                /*       rowBody.setHeightInPoints(20);*/


                for (Field f : fields) {

                    f.setAccessible(true);

                    Object va = f.get(obj);
                    if (null == va) {
                        va = "";
                    }
                    String fieldName = f.getName();
                    //设置单元格值
                    if (indexMap.containsKey(fieldName)) {
                        hc = rowBody.createCell(indexMap.get(fieldName));
                        hc.setCellStyle(cellStyle3);

                        String cellValue;
                        if (f.getType().equals(java.util.Date.class)) {
                            Annotation annotation = f.getAnnotation(JsonFormat.class);
                            if (annotation != null && !va.equals("")) {
                                JsonFormat jsonFormat = (JsonFormat) annotation;
                                cellValue = new SimpleDateFormat(jsonFormat.pattern()).format(va);
                            } else cellValue = va.toString();
                        } else if (columnFormatMaps != null && columnFormatMaps.containsKey(fieldName)) {
                            //format
                            Map<String, String> formatMap = columnFormatMaps.get(fieldName);
                            cellValue = formatMap.get(va.toString());
                        } else cellValue = va.toString();

                        hc.setCellValue(cellValue);
                    }
                }
                i++;
            }

            out = new ByteArrayOutputStream();
            wb.write(out);
            result = out.toByteArray();
        } catch (Exception ex) {
            Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (null != out) {
                    out.close();
                }
            } catch (IOException ex) {
                Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                try {
                    wb.close();
                } catch (IOException ex) {
                    Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return result;
    }

    private static String EXTENSION_XLS = "xls";
    private static String EXTENSION_XLSX = "xlsx";
    private static SimpleDateFormat SDF_FULL = new SimpleDateFormat("yy-MM-dd HH:mm:ss");//小写的mm表示的是分钟
    private static SimpleDateFormat SDF_NORMAL = new SimpleDateFormat("yy-MM-dd HH:mm");//小写的mm表示的是分钟
    private static SimpleDateFormat SDF_SHORT = new SimpleDateFormat("yy-MM-dd");//小写的mm表示的是分钟

    /**
     * excel转换成对象list
     *
     * @param rowStart
     * @param fileExtension
     * @param inputStream
     * @param map
     * @param clas
     * @return
     * @throws IOException
     */
    @Deprecated
    public static List transferExcelToEntity(int rowStart, String fileExtension, InputStream inputStream, Map<String, Integer> map, Map<String, Map<String, String>> refectMap, Class clas) throws Exception {
        // 获取workbook对象
        Workbook workbook = null;
        List<Object> resList = new ArrayList<>();
        //获取成员变量
        Field[] fields = clas.getDeclaredFields();
        //获取成员变量Map<name,type>
        Map<String, Class> fieldMap = new HashMap();
        for (int i = 0; i < fields.length; i++) {
            fieldMap.put(fields[i].getName(), fields[i].getType());
        }
        try {
            //构造excel数据对象
            workbook = getWorkbook(fileExtension, inputStream);
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                int firstRowIndex = sheet.getFirstRowNum();//初始行
                int lastRowIndex = sheet.getLastRowNum();//末行
                //int counter = sheet.getPhysicalNumberOfRows(); //获得总行数
                if (firstRowIndex == lastRowIndex)
                    continue;
                StringBuilder sb = new StringBuilder();//用于拼接方法名
                for (int rowIndex = rowStart; rowIndex <= lastRowIndex; rowIndex++) {
                    Row currentRow = sheet.getRow(rowIndex);// 当前行
                    //空行跳过
                    if (currentRow == null) {
                        continue;
                    }
                    Object entity = clas.newInstance();//存储实体
                    String cellValue = getCellValue(currentRow.getCell(1));
                    if (cellValue == null || cellValue.equals(""))
                        continue;
                    for (String key : map.keySet()) {
                        //首字母大写
                        char[] cs = key.toCharArray();
                        cs[0] -= 32;
                        //获取set方法名
                        String setMethodName = String.valueOf(sb.append("set").append(String.valueOf(cs)));
                        sb.delete(0, sb.length());
                        Class fieldType = fieldMap.get(key);
                        Method setMethod = clas.getMethod(setMethodName.toString(), fieldType);
                        //获取参数类型
                        Object[] args = new Object[1];
                        String val = getCellValue(currentRow.getCell(map.get(key)));
                        if (refectMap != null && refectMap.containsKey(key))
                            val = refectMap.get(key).get(val);
                        args[0] = getValue(val, fieldType);
                        setMethod.invoke(entity, args[0]);
                    }

                    resList.add(entity);
                }
            }
        } catch (Exception e) {
            log.error("transfer-error", e);
            throw e;
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("inputStream.close", e);
                }
            }
            inputStream.close();
        }
        return resList;
    }

    /**
     * 取单元格的值
     *
     * @param cell 单元格对象
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String cellValue = "";
        try {
            if (cell.getCellType() == CellType.BOOLEAN) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == CellType.NUMERIC) {
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
            } else {
                cellValue = String.valueOf(cell.getStringCellValue());
            }
        } catch (Exception e) {
            log.error("取单元格的值", e);
        }

        if (cellValue == null)
            return "";
        else if (cellValue.equals("null"))
            return null;
        else
            return cellValue.trim();
    }

    /**
     * 根据成员变量类型动态转换
     *
     * @param s
     * @param clas
     * @return
     */
    private static Object getValue(String s, Class clas) throws Exception {
        try {
            if (clas.getName().equals("java.lang.Long")) {
                return Long.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Integer")) {
                return Integer.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Double")) {
                return Double.valueOf(s);
            }
            if (clas.getName().equals("java.lang.Boolean")) {
                return Boolean.valueOf(s);
            }
            if (clas.getName().equals("java.util.Date")) {
                try {
                    return SDF_FULL.parse(s);
                } catch (Exception e) {
                    try {
                        return SDF_NORMAL.parse(s);
                    } catch (Exception e1) {
                        return SDF_SHORT.parse(s);
                    }
                }
            }
            if (clas.getName().equals("java.lang.Byte")) {
                return Byte.valueOf(s);
            } else return s == null ? "" : s;
        } catch (ParseException p) {
            log.error("ParseException", p);
            throw p;
        }
    }

    /**
     * 获取数据表
     *
     * @param fileExtension
     * @param inputStream
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(String fileExtension, InputStream inputStream) throws Exception {
        Workbook workbook;
        //构造excel数据对象
        if (EXTENSION_XLS.equals(fileExtension.toLowerCase())) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (EXTENSION_XLSX.equals(fileExtension.toLowerCase())) {
            workbook = new XSSFWorkbook(inputStream);
        } else throw new Exception("excel格式错误，无法打开");
        return workbook;
    }
}
